IF NOT(EXISTS(SELECT name FROM sys.databases WHERE name = 'SGLL')) CREATE DATABASE [SGLL];
GO
USE [SGLL];
GO
---
---Creating data structure for Table Requests
---
IF EXISTS(SELECT name FROM sys.tables WHERE name = 'TYPES_OF_SAMPLES') DROP TABLE TYPES_OF_SAMPLES;
CREATE TABLE TYPES_OF_SAMPLES
(
	ID TINYINT IDENTITY(1,1) PRIMARY KEY NOT NULL,
	DESCRIPTION VARCHAR(100) NOT NULL UNIQUE,
	IS_ACTIVE BIT NOT NULL,
	INSERTED DATETIME NOT NULL,
	UPDATED DATETIME NOT NULL
);
---
---Creating data structure for Table Requests
---
IF EXISTS(SELECT name FROM sys.tables WHERE name = 'COMPLAINTS') DROP TABLE COMPLAINTS;
CREATE TABLE COMPLAINTS
(
	ID BIGINT IDENTITY(20111206000,1) PRIMARY KEY NOT NULL, --(PK) Range [1 - 2,147,483,647]
	TYPE_OF_SAMPLE_ID TINYINT REFERENCES TYPES_OF_SAMPLES(ID) NOT NULL,
	OPEN_BY_USER VARCHAR(60) NOT NULL,
	DOCTOR_ID VARCHAR(60) NOT NULL,
	DATE_OF_COLLECTION DATETIME NOT NULL,
	COMMENTS VARCHAR(255) NULL,
	DEPARTAMENT VARCHAR(19) NOT NULL,
	CLOSE_BY_USER VARCHAR(60) NULL,
	INSERTED DATETIME DEFAULT GETDATE() NOT NULL,
	UPDATED DATETIME DEFAULT GETDATE() NOT NULL
);